package com.cpimex.eprice.model;

import KC.utils.Mail;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Statement;
import java.util.Map;
import java.util.Properties;
import java.sql.Connection;
import java.sql.ResultSet;

import javax.naming.InitialContext;
import javax.sql.DataSource;


/**
 * 
 * @author Luis
 *
 */
public class Correo {
	
	private static final String VOID = "";
	private static boolean DEBUG = false ;
	private static Properties properties;
	/**
	 * Fija paramteros de envio de correos
	 * 
	 * @param map_parametros
	 * @return
	 */
    private static Mail configuracionMain( Map map_parametros )throws Exception{
        Mail mail_retVal = new Mail();
        mail_retVal.setAuthentication(true);
        mail_retVal.setMailAut((String) map_parametros.get("mailAut"));
        mail_retVal.setPasswordFrom((String) map_parametros.get("passwordFrom"));
        mail_retVal.setMailFrom((String) map_parametros.get("mailFrom"));
        mail_retVal.setHost((String) map_parametros.get("host"));

        return mail_retVal;
    }
    
    /**
     * Envio de correos pr detalle de Solicitud
     * 
     * @param stmt
     * @param map_param
     * @param int_idDetSol
     * @param int_estatus
     * @param str_from
     * @throws Exception
     */
    public static void detalleSolicitud(Connection conn, Map map_param, int int_idDetSol, int int_estatus, String str_from) throws Exception {
    
    	Statement stmt = (conn).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    	Statement stmt2 = (conn).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    	try{
    		
	        String str_subject    = null;
	        String str_body       = "";
	        String str_codigoCli  = "";
	        String str_descCli    = "";
	        String str_codigoProd = "";
	        String str_descProd   = "";
	        String str_idDirigido = "";
	        String str_email      = "";
	        String str_cliente    = "";
	        String str_producto   = "";
	        boolean bool_isAgro;
	        int int_logistica;
	        String str_subjectExtra = "";
	        String str_otroEmail = "";
	        String perfil = "";
	        
	        boolean bool_body = false;
	               
	        String str_query = "SELECT a.id_dirigidoCorreo,b.desc_perfil FROM ep_matrizCorreo a, ep_vw_perfil b WHERE b.id_perfil = a.id_perfil AND a.id_estatus = "+ int_estatus;
            ResultSet rs3 = stmt.executeQuery(str_query);
            while(rs3.next())
            {
            	str_query="";
            	perfil = rs3.getString("desc_perfil");
            	str_idDirigido = rs3.getString("id_dirigidoCorreo");
            	if(perfil.equals("VPO")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod,  "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, uv.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS uv ON uv.nomina_usuario = (CASE WHEN vpobackup_param <> '' AND vpobackup_param IS NOT NULL "+ 
                      "THEN vpobackup_param ELSE pa.vpo_param END) " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus  "+
                      "WHERE ds.id_detsol =  "+int_idDetSol;
            	}else if(perfil.equals("Gerente") || perfil.equals("ResponsableGerencia") ){
            		str_query = "SELECT  s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod,  "+
                    "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, "+
                    "ug.email_usuario AS email,oc.email_usuario "+
                    "FROM  dbo.ep_solicitud AS s INNER JOIN "+
                    "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                    "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                    "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                    "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                    "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                    "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                    "dbo.ep_vw_usuario AS ug ON ug.nomina_usuario = (CASE WHEN cp.backup_gte <> '' AND cp.backup_gte IS NOT NULL "+ 
                    "THEN cp.backup_gte WHEN cp.codigo_gte <> '' AND cp.codigo_gte IS NOT NULL THEN cp.codigo_gte ELSE pa.gerente_param END) " +
                    "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus  "+					  
                    "WHERE ds.id_detsol = "+int_idDetSol;
            	}else if(perfil.equals("Ejecutivo")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, us.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS us ON s.codigoVen_sol = us.nomina_usuario " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+ 
                      "WHERE ds.id_detsol =  "+int_idDetSol;
            	}else if(perfil.equals("Coordinador")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro,   "+
                      "uc.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS uc ON co.nomina_coor = uc.nomina_usuario " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+ 
                      "WHERE ds.id_detsol =  "+int_idDetSol;
            	}else if(perfil.equals("Director")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro,ud.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS ud ON ud.nomina_usuario = (CASE WHEN dirbackup_param <> '' AND dirbackup_param IS NOT NULL "+ 
                      "THEN dirbackup_param ELSE pa.director_param END)  " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+
                      "WHERE ds.id_detsol =  "+int_idDetSol;
            	}else if(perfil.equals("GerenteRegional")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, "+
                      "ugr.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS ugr ON ugr.nomina_usuario = ds.gteRegAuto_detlSol " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+
                      "WHERE ds.id_detsol =  "+int_idDetSol;
            	}
            	if(!str_query.equals("")){
            		ResultSet rs = stmt2.executeQuery(str_query);
			        Mail mail = configuracionMain(map_param);
			        while (rs.next()) {
			            str_codigoCli  = rs.getString("codigo_cli");
			            str_descCli    = rs.getString("descripcion_cli");
			            str_cliente    = str_codigoCli + " - " + str_descCli;
			            str_codigoProd = rs.getString("codigo_pro");
			            str_descProd   = rs.getString("descripcion_prod");
			            str_producto   = str_codigoProd + " - " + str_descProd;
			            str_email      = rs.getString("email") == null ? "" : rs.getString("email");
			            bool_isAgro    = rs.getBoolean("is_agro");
			            int_logistica  = rs.getInt("logistica");
			            str_otroEmail  = rs.getString("email_usuario") == null ? "" : rs.getString("email_usuario");
			            str_subjectExtra  =  " Cliente: " +str_codigoCli+" - " + "Producto: " +str_codigoProd+ (bool_isAgro?" - " + String.valueOf(int_logistica):VOID);
			            
			            if(!bool_body){
			            	if(int_estatus ==1){
			                    str_subject = "Aviso del Sistema ePrice" + str_subjectExtra;
			                    str_body = " Solicitud nueva en espera de enviar a autorizaci&oacute;n Cliente : <br>";
			                    str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
				            }else if(int_estatus ==2){
				            	str_subject = "Aviso del Sistema ePrice" + str_subjectExtra;
				            	str_body = " Aviso de Espera de Autorizacion de Solicitud de nivel Gerencia";
				            	// str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
				            	str_body +=  "<br> Gerente: El vendedor desea la autorizaci&oacute;n del precio. <br> ";
				            }else if(int_estatus == 3){
				            	str_subject = "Aviso del Sistema ePrice" + str_subjectExtra;
				            	str_body += " Aviso de espera de Autorizaci&oacute;n de Solicitd de Nivel Direcci&oacute;n";
				            	//str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
				            	str_body += " <br>Director: El vendedor desea la autorizaci&oacute;n del precio";
				            }else if(int_estatus == 4){
			                     str_subject = "Aviso del Sistema ePrice" + str_subjectExtra;
			                     str_body = " Aviso de Espera de Autorizaci&oacute;n de Solicitud de Nivel VPO <br> ";
			                     //str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
			                     str_body += "VPO : El Vendedor desea la autorizaci&oacute;n ";
				            }else if(int_estatus ==5){
			                    str_subject = "Aviso del Sistema ePrice" + str_subjectExtra;
			                    str_body += " Autorizaci&oacute;on de solicitud <br> ";
			                    str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br><br> ";
			                    str_body += "Vendedor Su solicitud ha sido autorizada";
				            }else if(int_estatus ==6){
			                    str_subject = "Rechazo Aviso del Sistema ePrice" + str_subjectExtra;
			                    str_body +=" Rechazo de Solicitud de Nivel Gerencia  <br>";
			                    //str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
			                    str_body +="<br> Vendedor su Solicitud ha sido rechazada. ";
				            }else if(int_estatus ==7){
			                    str_subject = "Rechazo Aviso del Sistema ePrice" + str_subjectExtra;
			                    str_body =  " Rechazo de Solicitud de Nivel Direccion <br> ";
			                    //str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
			                    str_body += "Vendedor su Solicitud ha sido rechazada. ";
				            }else if(int_estatus ==26){
			                    str_subject = "" +
			                    		"Rechazo Aviso del Sistema ePrice" + str_subjectExtra;
			                    str_body = " Rechazo de Solicitud de Nivel VPO<br>";
			                    //str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
			                    str_body += "Vendedor su Solicitud ha sido rechazada. ";
				            }else if(int_estatus ==38){    // Codigo DEAC 13032009
				            	str_subject = "Aviso del Sistema ePrice" + str_subjectExtra;
				            	str_body = " Aviso de Espera de Autorizacion de Solicitud de nivel Gerencia Regional"; 
				            	// str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
				            	str_body +=  "<br> Gerente: El vendedor desea la autorizaci&oacute;n del precio. <br> ";
				            }else if(int_estatus ==39){
			                    str_subject = "" +
		                		"Rechazo Aviso del Sistema ePrice" + str_subjectExtra;
			                    str_body = " Rechazo de Solicitud de Nivel Gerente Regional<br>";
			                    //str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") <br> ";
			                    str_body += "Vendedor su Solicitud ha sido rechazada. ";
				            }                                              // Fin Codigo DEAC 13032009
			            	str_body += "<br><br><br>";
			            }
			           
			           
			            if(DEBUG){System.out.println(str_email + ", " + str_otroEmail);}
			            if (str_idDirigido.equals("1")) {
			            	if(!str_email.equals(VOID)){mail.addMailTo(str_email);}else{mail.addMailTo(str_email);}
			            } else {
			            	if(!str_email.equals(VOID)){mail.addMailCC(str_email);}else{mail.addMailCC(str_email);}
			            }
			            if(!bool_body){
			            	if(!str_otroEmail.equals(VOID)){mail.addMailTo(str_otroEmail);}
			            }
			            bool_body = true;
			        }
		        	mail.setMailFrom("eprice@cpimex.com");
		            mail.setSubject(str_subject);
		            mail.setBody(str_body);                  
		            System.out.println(mail.sendMail());
            	}
            }
            
    	}
    	catch(Exception ex){
    		throw ex;
    	}finally{
    		stmt.close();
    	}
    }

    
    /**
     * Envio de correos por solicitud
     * 
     * @param conn
     * @param map_param
     * @param int_idSol
     * @param int_estatus
     * @throws Exception
     */
    public static void solicitud(Connection conn, Map map_param, int int_idSol, int int_estatus) throws Exception {
    	Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    	Statement stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    	try{
    		String str_body = "";
            String str_subject = "";
            String str_codigoCli = "";
            String str_descCli = "";
            String str_codigoProd = "";
            String str_descProd = "";
            String str_idDirigido = "";
            String str_email = "";
            String str_otroEmail = "";
            String str_cliente = "";
            String str_producto = "";
            String perfil = "";
            /*
            String str_query =
                    " SELECT cs.id_estatus,                                                " +
                    "        es.codigo_estatus,                                            " +
                    "        cs.codigo_cli,                                                " +
                    "        cs.descripcion_cli,                                           " +
                    "        cs.codigo_pro,                                                " +
                    "        cs.descripcion_prod,                                          " +
                    "        p.desc_perfil,                                                " +
                    "        mc.id_dirigidoCorreo,                                         " +
                    "        CASE                                                          " +
                    "            WHEN desc_perfil = 'VPO' THEN email_vpo                   " +
                    "            WHEN desc_perfil = 'Gerente' THEN email_gerente           " +
                    "            WHEN desc_perfil = 'Ejecutivo' THEN email_solicitante     " +
                    "            WHEN desc_perfil = 'Coordinador' THEN email_coordinadora  " +
                    "            WHEN desc_perfil = 'Director' THEN email_director         " +
                    "            WHEN desc_perfil = 'ResponsableGerencia' THEN email_gerente         " + // Codigo DEAC 13032009
                    "            WHEN desc_perfil = 'GerenteRegional' THEN email_gerenteregional         " + // Codigo DEAC 13032009
                    "        END email,                                                    " +
                    "        email_usuario                                                 " +
                    " FROM ep_vw_correosSolicitud cs                                       " +
                    "      INNER JOIN ep_matrizCorreo mc                                   " +
                    "              ON cs.id_estatus = mc.id_estatus                        " +
                    "      INNER JOIN ep_vw_perfil p                                       " +
                    "              ON mc.id_perfil = p.id_perfil                           " +
                    "      INNER JOIN ep_estatus es                                        " +
                    "              ON cs.id_estatus = es.id_estatus                        " +
                    "	   LEFT JOIN ep_vw_estatusOtroCorreo oc                            " +
                    "			   ON oc.id_estatus = mc.id_estatus                        " +
                    "      WHERE id_sol = " + int_idSol + "                                " +
                    " ORDER BY mc.id_dirigidoCorreo,  email                                ";
            
            if(DEBUG){System.out.println(str_query);}
            */
            
            String str_query = "SELECT a.id_dirigidoCorreo,b.desc_perfil FROM ep_matrizCorreo a, ep_vw_perfil b WHERE b.id_perfil = a.id_perfil AND a.id_estatus = "+ int_estatus;
            ResultSet rs2 = stmt.executeQuery(str_query);
            while (rs2.next())
            {
            	str_query="";
            	perfil = rs2.getString("desc_perfil");
            	str_idDirigido  = rs2.getString("id_dirigidoCorreo");
            	if(perfil.equals("VPO")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod,  "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, uv.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS uv ON uv.nomina_usuario = (CASE WHEN vpobackup_param <> '' AND vpobackup_param IS NOT NULL "+ 
                      "THEN vpobackup_param ELSE pa.vpo_param END) " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus  "+
                      "WHERE s.id_sol =  "+int_idSol;
            	}else if(perfil.equals("Gerente") || perfil.equals("ResponsableGerencia") ){
            		str_query = "SELECT  s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod,  "+
                    "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, "+
                    "ug.email_usuario AS email,oc.email_usuario "+
                    "FROM  dbo.ep_solicitud AS s INNER JOIN "+
                    "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                    "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                    "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                    "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                    "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                    "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                    "dbo.ep_vw_usuario AS ug ON ug.nomina_usuario = (CASE WHEN cp.backup_gte <> '' AND cp.backup_gte IS NOT NULL "+ 
                    "THEN cp.backup_gte WHEN cp.codigo_gte <> '' AND cp.codigo_gte IS NOT NULL THEN cp.codigo_gte ELSE pa.gerente_param END) " +
                    "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus  "+					  
                    "WHERE s.id_sol = "+int_idSol;
            	}else if(perfil.equals("Ejecutivo")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, us.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS us ON s.codigoVen_sol = us.nomina_usuario " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+ 
                      "WHERE s.id_sol =  "+int_idSol;
            	}else if(perfil.equals("Coordinador")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro,   "+
                      "uc.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS uc ON co.nomina_coor = uc.nomina_usuario " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+ 
                      "WHERE s.id_sol =  "+int_idSol;
            	}else if(perfil.equals("Director")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro,ud.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS ud ON ud.nomina_usuario = (CASE WHEN dirbackup_param <> '' AND dirbackup_param IS NOT NULL "+ 
                      "THEN dirbackup_param ELSE pa.director_param END)  " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+
                      "WHERE s.id_sol =  "+int_idSol;
            	}else if(perfil.equals("GerenteRegional")){
            		str_query = "SELECT     s.id_sol, ds.id_detSol, ds.id_estatus, ds.codigoCli_detSol AS codigo_cli, c.descripcion_cli, ds.codigoPro_detSol AS codigo_pro, p.descripcion_prod, "+ 
                      "ds.logistica_detSol AS logistica, CASE WHEN clase_prod = 'Agroproductos' THEN 1 ELSE 0 END AS is_agro, "+
                      "ugr.email_usuario AS email,oc.email_usuario "+
                      "FROM         dbo.ep_solicitud AS s INNER JOIN "+
                      "dbo.ep_detalleSolicitud AS ds ON s.id_sol = ds.id_sol LEFT OUTER JOIN "+
                      "dbo.ep_vw_producto AS p ON ds.codigoPro_detSol = p.codigo_prod LEFT OUTER JOIN "+
                      "dbo.ep_claseProducto AS cp ON p.clase_prod = cp.codigo_clProd LEFT OUTER JOIN "+
                      "dbo.ep_vw_cliente AS c ON ds.codigoCli_detSol = c.codigo_cli LEFT OUTER JOIN "+
                      "dbo.ep_vw_coordinadora AS co ON c.coordinadora_cli = co.codigo_coor LEFT OUTER JOIN "+
                      "dbo.ep_parametro AS pa ON 1 = 1 LEFT OUTER JOIN "+
                      "dbo.ep_vw_usuario AS ugr ON ugr.nomina_usuario = ds.gteRegAuto_detlSol " +
                      "LEFT JOIN ep_vw_estatusOtroCorreo oc ON oc.id_estatus = ds.id_estatus "+
                      "WHERE s.id_sol =  "+int_idSol;
            	}
            	if(!str_query.equals("")){
            		ResultSet rs = stmt2.executeQuery(str_query);
            		Mail mail = configuracionMain(map_param);
            		
            		//------------Inicio codigo dario
            		while (rs.next())
                    {
                    	
                        str_codigoCli   = rs.getString("codigo_cli");
                        str_descCli     = rs.getString("descripcion_cli");
                        str_cliente     = str_codigoCli + " - " + str_descCli;
                        str_codigoProd  = rs.getString("codigo_pro");
                        str_descProd    = rs.getString("descripcion_prod");
                        str_producto    = str_codigoProd + " - " + str_descProd;
                        //str_idDirigido  = rs.getString("id_dirigidoCorreo");
                        str_email       = rs.getString("email") == null ? "" : rs.getString("email");
                        str_otroEmail   = rs.getString("email_usuario") == null ? "" : rs.getString("email_usuario");

                        if(int_estatus ==1)
                        {
                            str_subject = "Aviso del Sistema ePrice";
                            if (str_body.equals(""))
                            {
                                str_body = " Solicitud nueva en espera de enviar a autorizaci&oacute;on Cliente </br>: ";
                            }
                            str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") </br> ";
                        }
                        else if(int_estatus ==2)
                        {
        	                str_subject = " Aviso del Sistema ePrice";
        	                if (str_body.equals(""))
        	                {
        	                    str_body = " Aviso de Espera de Autorizaci&oacute;n de Nivel Gerencia del Sistema ePrice </br> ";
        	                }
        	                str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") </br> ";
        	
        	                if(rs.isLast())
        	                {
        	                    str_body +=  "</br> Gerente : El Vendedor desea la autorizaci&oacute;n ";
        	                }
                        } 
                        else if(int_estatus ==38)
                        { // Codigo DEAC 13032009
        	                str_subject = " Aviso del Sistema ePrice";
        	                if (str_body.equals(""))
        	                {
        	                    str_body = " Aviso de Espera de Autorizaci&oacute;n de Nivel Gerencia Regional del Sistema ePrice </br> ";
        	                }
        	                str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") </br> ";
        	
        	                if(rs.isLast())
        	                {
        	                    str_body +=  "</br> Gerente : El Vendedor desea la autorizaci&oacute;n ";
        	                }
                        } // Fin Codigo DEAC 13032009
                        str_body += "<br><br><br>";
                        
                        if (rs.next()) {
                            String str_mailNext = rs.getString("email") == null ? "" : rs.getString("email");
                            if (!str_email.equals(str_mailNext)) {
                            	mail = configuracionMain(map_param);
                            	mail.setMailFrom("eprice@cpimex.com");
                            	// System.out.println(str_email + "\n" +str_otroEmail + "\n"+ str_subject + "\n" + str_body);
                                mail.setSubject(str_subject);
                                mail.setBody(str_body);
                                
                                if (str_idDirigido.equals("1")){
                                    mail.addMailTo(str_email);
                                } else{
                                    mail.addMailCC(str_email);
                                    
                                }
                                if(str_otroEmail!=null){
                                	 mail.addMailCC(str_otroEmail);
                                }
                               System.out.println(mail.sendMail());
                                str_body = "";
                            } else{
                                str_body += "<br>";
                            }
                            rs.previous();
                        } else {
                        	mail = configuracionMain(map_param);
                        	mail.setMailFrom("eprice@cpimex.com");
                            mail.setSubject(str_subject);
                            mail.setBody(str_body);
                            
                            if (str_idDirigido.equals("1")){
                                mail.addMailTo(str_email);
                            } else{
                                mail.addMailCC(str_email);
                            }
                            if(str_otroEmail!=null){
                            	 mail.addMailCC(str_otroEmail);
                            }
                           System.out.println(mail.sendMail());
                        }
                    }
            		
            		//--------------------------------
            	}
            	
            }
            
            
            /*
            Mail mail = configuracionMain(map_param);

            while (rs.next())
            {
            	
                str_codigoCli   = rs.getString("codigo_cli");
                str_descCli     = rs.getString("descripcion_cli");
                str_cliente     = str_codigoCli + " - " + str_descCli;
                str_codigoProd  = rs.getString("codigo_pro");
                str_descProd    = rs.getString("descripcion_prod");
                str_producto    = str_codigoProd + " - " + str_descProd;
                str_idDirigido  = rs.getString("id_dirigidoCorreo");
                str_email       = rs.getString("email") == null ? "" : rs.getString("email");
                str_otroEmail   = rs.getString("email_usuario") == null ? "" : rs.getString("email_usuario");

                if(int_estatus ==1)
                {
                    str_subject = "Aviso del Sistema ePrice";
                    if (str_body.equals(""))
                    {
                        str_body = " Solicitud nueva en espera de enviar a autorizaci&oacute;on Cliente </br>: ";
                    }
                    str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") </br> ";
                }
                else if(int_estatus ==2)
                {
	                str_subject = " Aviso del Sistema ePrice";
	                if (str_body.equals(""))
	                {
	                    str_body = " Aviso de Espera de Autorizaci&oacute;n de Nivel Gerencia del Sistema ePrice </br> ";
	                }
	                str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") </br> ";
	
	                if(rs.isLast())
	                {
	                    str_body +=  "</br> Gerente : El Vendedor desea la autorizaci&oacute;n ";
	                }
                } 
                else if(int_estatus ==38)
                { // Codigo DEAC 13032009
	                str_subject = " Aviso del Sistema ePrice";
	                if (str_body.equals(""))
	                {
	                    str_body = " Aviso de Espera de Autorizaci&oacute;n de Nivel Gerencia Regional del Sistema ePrice </br> ";
	                }
	                str_body += "(" + str_cliente + ") Producto : (" + str_producto + ") </br> ";
	
	                if(rs.isLast())
	                {
	                    str_body +=  "</br> Gerente : El Vendedor desea la autorizaci&oacute;n ";
	                }
                } // Fin Codigo DEAC 13032009
                str_body += "<br><br><br>";
                
                if (rs.next()) {
                    String str_mailNext = rs.getString("email") == null ? "" : rs.getString("email");
                    if (!str_email.equals(str_mailNext)) {
                    	mail = configuracionMain(map_param);
                    	mail.setMailFrom("eprice@cpimex.com");
                    	// System.out.println(str_email + "\n" +str_otroEmail + "\n"+ str_subject + "\n" + str_body);
                        mail.setSubject(str_subject);
                        mail.setBody(str_body);
                        
                        if (str_idDirigido.equals("1")){
                            mail.addMailTo(str_email);
                        } else{
                            mail.addMailCC(str_email);
                            
                        }
                        if(str_otroEmail!=null){
                        	 mail.addMailCC(str_otroEmail);
                        }
                       System.out.println(mail.sendMail());
                        str_body = "";
                    } else{
                        str_body += "<br>";
                    }
                    rs.previous();
                } else {
                	mail = configuracionMain(map_param);
                	mail.setMailFrom("eprice@cpimex.com");
                    mail.setSubject(str_subject);
                    mail.setBody(str_body);
                    
                    if (str_idDirigido.equals("1")){
                        mail.addMailTo(str_email);
                    } else{
                        mail.addMailCC(str_email);
                    }
                    if(str_otroEmail!=null){
                    	 mail.addMailCC(str_otroEmail);
                    }
                   System.out.println(mail.sendMail());
                }
            }
            */
            
    	}catch(Exception ex){
    		ex.printStackTrace(System.out);
    		throw ex;
    	}finally{
    		stmt.close();
    	}
    }

    
    
	/**
	 * 
	 *
	 */    
    protected static void propertiesReader()throws Exception{
		java.io.File f = new java.io.File("text.properties");
		f.createNewFile();
    	properties = new Properties();
		try{
			properties.load(new FileInputStream("Mailer.properties"));
		} catch (IOException e) {
			e.printStackTrace(System.out);
			throw e;
		}
	}
}
